# _*_ coding: utf-8_*_

import time
import urllib2
import re
import pandas as pd
from lxml import etree
import mysql.connector

import sys
reload(sys)
sys.setdefaultencoding('utf-8')


config = {
    'host': '123.207.19.115',
    'user': 'root',
    'password': '123456',
    'port': 3306,
    'database': 'tourism',
    'charset': 'utf8'
}


# 获取链接中的网页内容
def get_page(url):
    # headers = {
    #    'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 '
    #                  '(KHTML, like Gecko) Chrome/53.0.2785.143 Safari/537.36'
    # }
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) '
                      'Chrome/34.0.1847.137 Safari/537.36 LBBROWSER'
    }
    try:
        request = urllib2.Request(url=url, headers=headers)
        response = urllib2.urlopen(request, timeout=5)
        page = response.read().decode('utf-8')
        return page
    except (urllib2.URLError, Exception), e:
        if hasattr(e, 'reason'):
            print '抓取失败，具体原因：', e.reason
            request = urllib2.Request(url=url, headers=headers)
            response = urllib2.urlopen(request, timeout=5)
            page = response.read().decode('utf-8')
            return page


def get_list(place):
    url = 'http://piao.qunar.com/ticket/list.htm?keyword='+str(place) +'&region=&from=mpl_search_suggest&sort=pp&page={}&subject=&sku='
    i = 1
    sight_list = []
    conn = mysql.connector.connect(host='123.207.19.115', user='root', password='123456',
                                   database='tourism', use_unicode=True)
    cursor = conn.cursor()
    while i < 20:
        page = get_page(url.format(i))
        selector = etree.HTML(page)
        information = selector.xpath('//div[@class="result_list"]/div')
        for inf in information:  # 获取必要信息
            sight_image = inf.xpath('./@data-sight-img-u-r-l')
            if len(sight_image):
                sight_image = str(sight_image[0])
            else:
                sight_image = '无数据'
            sight_name = inf.xpath('./div/div/h3/a/text()')
            if len(sight_name):
                sight_name = str(sight_name[0])
            else:
                sight_name = '无数据'
            sight_level = inf.xpath('.//span[@class="level"]/text()')
            if len(sight_level):
                sight_level = str(sight_level[0].replace('景区', ''))
            else:
                sight_level = '无数据'
            sight_area = inf.xpath('.//span[@class="area"]/a/text()')
            if len(sight_area):
                sight_area = str(sight_area[0])
            else:
                sight_area = '无数据'
            sight_hot = inf.xpath('.//span[@class="product_star_level"]//span/text()')
            if len(sight_hot):
                sight_hot = str(sight_hot[0])
            else:
                sight_hot = '无数据'
            sight_add = inf.xpath('.//p[@class="address color999"]/span/text()')
            if len(sight_add):
                sight_add = str(re.sub('地址：|（.*?）|\(.*?\)|，.*?$|\/.*?$', '', str(sight_add[0])))
            else:
                sight_add = '无数据'
            sight_soldnum = inf.xpath('.//span[@class="hot_num"]/text()')
            if len(sight_soldnum):
                sight_soldnum = str(sight_soldnum[0])
            else:
                sight_soldnum = '无数据'
            sight_point = inf.xpath('./@data-point')
            if len(sight_point):
                sight_point = str(sight_point[0])
            else:
                sight_point = '无数据'
            sight_url = inf.xpath('.//h3/a[@class="name"]/@href')
            if len(sight_url):
                sight_url = str('http://piao.qunar.com') + sight_url[0]
            else:
                sight_url = '无数据'
            sight_slogen = inf.xpath('.//div[@class="intro color999"]/text()')
            if len(sight_slogen):
                sight_slogen = str(sight_slogen[0])
            else:
                sight_slogen = '无数据'
            sight_price = inf.xpath('.//span[@class="sight_item_price"]/em/text()')
            if len(sight_price):
                sight_price = str(sight_price[0])
            else:
                sight_price = '无数据'
            data = (sight_name, sight_level, sight_area, sight_image, sight_price, sight_soldnum, sight_hot, sight_add, sight_point, sight_slogen, sight_url, place)
            cursor.execute('insert into tb_scenic_city(title,lev,area,image,price,sold,hot,address,point,slogen,url,type) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)', data)
            sight_list.append(
                [sight_name, sight_level, sight_area, sight_image, sight_price, sight_soldnum, sight_hot,
                 sight_add, sight_point, sight_slogen, sight_url,place])
        time.sleep(10)
        i += 1
    conn.commit()
    conn.close()
    return sight_list


def list_to_excel(data, name):
    # type: (object, object) -> object
    df = pd.DataFrame(data, columns=['景点名称', '级别', '所在区域', '缩略图', '起步价', '销售量', '热度', '地址', '经纬度', '标语', '详情网址', '城市'])
    df.to_excel(name + 'info.xlsx')



